package com.mayn.jst.taobao;

import java.util.Date;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import com.dtgrid.jfinal.Convert;
import com.jfinal.plugin.activerecord.Db;
import com.jfinal.plugin.activerecord.Record;
import com.mayn.jst.login.TaobaoLoginAction;
import com.mayn.jst.shop.action.ShopAccountAction;
import com.mayn.jst.shop.action.ShopAction;
import com.mayn.jst.shop.model.ShopAccountModel;
import com.mayn.util.DateUtil;
import com.mayn.util.SendCloudMailUtil;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

/**
 * 淘宝DSR采集处理
 * 
 * @author Administrator
 *
 */
public class TaobaoDsrAction {

	private static final Logger LOG = LoggerFactory.getLogger(TaobaoDsrAction.class);

	/**
	 * @return 
	 * 
	 */
	public static Boolean getAllShopDsrData() {
		List<ShopAccountModel> shopAccountList = ShopAccountAction.getShopAccountList();
		for (int i = 0; i < shopAccountList.size(); i++) {
			int shopId = shopAccountList.get(i).getInt("SA_SHOP_ID");
			int shopType = ShopAction.getShopType(shopId);
			switch (shopType) {
			case 1:
				String statis_date = DateUtil.dateFmtToString(new Date(), "yyyy-MM-dd");
				TaobaoDsrAction.getDsrDataByShop(shopId,statis_date);
				break;
			default:
				break;
			}

		}
		return true;
	}

	/**采集指定店铺宝贝DSR数据
	 * @param shopId
	 * @param statis_date
	 * @return
	 */
	public static Boolean getDsrDataByShop(int shopId,String statis_date) {
		String shopName = ShopAction.getShop(shopId).getStr("SHOP_NAME");
		if(checkDsrDataByDate(shopId,statis_date)){
			LOG.error("当前店铺dsr数据已采集--" + shopName);
			return false;
		}
		if (TaobaoLoginAction.checkAndSaveTaobaoLoginCookie(shopId)) {
			LOG.debug("当前店铺cookie依然有效--" + shopName);
		} else {
			LOG.error("当前店铺cookie无法获取--" + shopName);
			return false;
		}
		String cookie = ShopAccountAction.getShopAccountCookie(shopId);
		JSONArray dataArray = TaobaoLoginAction.getDsrJsonArray(cookie);
		
		if (dataArray.size() > 0) {
			String sql = "UPDATE shop_product_dsr_statis SET SPDS_DEL = 1 WHERE SPDS_STATIS_DATE='" + statis_date + "'";
			Db.update(sql);
		}
		for (int i = 0; i < dataArray.size(); i++) {
			JSONObject item = dataArray.getJSONObject(i);
			String item_id = item.getString("item_id");
			String item_pic = item.getString("item_pic");
			String item_title = item.getString("item_title");
			Date item_date = DateUtil.stringToDate(item.getString("item_date"), "yyyy-mm-dd");
			double dsr = 0;
			try {
				if(item.containsKey("dsr")){
					dsr = item.getDouble("dsr");
				}
			} catch (Exception e) {
				LOG.error(e.getMessage() + item.getString("dsr"));
			}
			saveProduct(shopId, item_id, item_pic, item_title, item_date, dsr);

			double manual_rate = item.getDouble("manual_rate");
			double review_cnt = item.getDouble("review_cnt");
			int review_cnt_up = item.getInt("review_cnt_up");
			double scan_rate_mobile = item.getDouble("scan_rate_mobile");
			double scan_rate_pc = item.getDouble("scan_rate_pc");

			saveDsrStatis(statis_date, shopId, item_id, dsr, manual_rate, review_cnt, review_cnt_up, scan_rate_mobile, scan_rate_pc);

			LOG.info(item_id + "--" + item_title + "--" + dsr);
		}
		if (dataArray.size() > 0) {
			LOG.info("采集成功");
			return true;
		} else {
			LOG.error("采集失败");
			String msg = "DSR数据采集失败，请检查";
			SendCloudMailUtil.sendMailCode(msg, "dadao@iningmei.com");
			return false;
		}
	}
	
	public static Boolean checkDsrDataByDate(int shopId,String statis_date){
		String sql = "select COUNT(*) from shop_product_dsr_statis spds inner join shop_product sp on sp.SP_ITEM_ID=spds.SPDS_ITEM_ID"
				+ " where 1=1 and spds.SPDS_DEL=0 and SP_SHOP_ID=? and SPDS_STATIS_DATE=?";
		int c = Db.queryLong(sql,shopId,statis_date).intValue();
		if(c>=1){
			return true;
		}
		return false;
	}

	public static JSONObject getDsrJsonObjectFromDb(String statis_date, int pageSize, int nowPage, Boolean All) {
		JSONObject dataObject = new JSONObject();
		JSONArray dataArray = new JSONArray();
		if (statis_date != null && statis_date.length() > 0) {
			LOG.debug("查询日期--" + statis_date);
		} else {
			statis_date = DateUtil.dateFmtToString(new Date(), "yyyy-MM-dd");
		}
		String sql = "";
		String countSql = "select COUNT(*) " + " from shop_product_dsr_statis spds inner join shop_product sp on sp.SP_ITEM_ID=spds.SPDS_ITEM_ID"
				+ " where 1=1 and spds.SPDS_DEL=0 and SPDS_STATIS_DATE='" + statis_date + "'";
		sql = "select SPDS_STATIS_DATE as statis_date,spds.SPDS_ITEM_ID as item_id,sp.SP_ITEM_TITLE as item_title,SPDS_DSR as dsr,spds.SPDS_REVIEW_CNT as review_cnt,spds.SPDS_REVIEW_CNT_UP as review_cnt_up,spds.SPDS_MANUAL_RATE as manual_rate,spds.SPDS_SCAN_RATE_MOBILE as scan_rate_mobile,spds.SPDS_SCAN_RATE_PC as scan_rate_pc,spds.SPDS_CREATE_DATETIME as create_date "
				+ " from shop_product_dsr_statis spds inner join shop_product sp on sp.SP_ITEM_ID=spds.SPDS_ITEM_ID" + " where 1=1 and spds.SPDS_DEL=0 and SPDS_STATIS_DATE='" + statis_date + "'";
		sql = sql + "order by spds.SPDS_CREATE_DATETIME desc";
		Long totalCount = Db.queryLong(countSql);
		if (!All) {
			sql = sql + " limit " + ((nowPage - 1) * pageSize + 1) + "," + pageSize + "";
		}
		List<Record> listRecord = Db.find(sql);
		dataArray = Convert.getJsonObjFromListRecord(listRecord);

		dataObject.put("data", dataArray);
		dataObject.put("total_count", totalCount);

		return dataObject;
	}

	public static void saveProduct(int shopId, String item_id, String item_pic, String item_title, Date item_date, double dsr) {
		String checkSql = "SELECT COUNT(*) FROM shop_product WHERE SP_ITEM_ID='" + item_id + "'";
		String insertSql = "INSERT INTO `shop_product` " + "(`SP_SHOP_ID`, `SP_ITEM_ID`, `SP_ITEM_PIC`, `SP_ITEM_TITLE`,`SP_ITEM_DATE`,`SP_DSR`,`SP_DEL`, `SP_CREATE_DATETIME`)" + " VALUES "
				+ "(?,?,?,?,?,? ,'0', now())";
		String updateSql = "UPDATE `shop_product` SET " + " SP_ITEM_PIC=?" + ",SP_ITEM_TITLE=?" + ",SP_ITEM_DATE=?" + ",SP_DSR=?" + ",SP_UPDATE_DATETIME=now()" + " WHERE SP_ITEM_ID=?";
		Long c = Db.queryLong(checkSql);
		if (c == 0) {
			Db.update(insertSql, shopId, item_id, item_pic, item_title, item_date, dsr);
		} else {
			Db.update(updateSql, item_pic, item_title, item_date, dsr, item_id);
		}

	}

	public static void saveDsrStatis(String statis_date, int shopId, String item_id, double dsr, double manual_rate, double review_cnt, int review_cnt_up, double scan_rate_mobile, double scan_rate_pc) {
		String insertSql = "INSERT INTO `shop_product_dsr_statis` "
				+ "(`SPDS_STATIS_DATE`, `SPDS_SHOP_ID`,`SPDS_ITEM_ID`,`SPDS_DSR`,`SPDS_MANUAL_RATE`, `SPDS_REVIEW_CNT`, `SPDS_REVIEW_CNT_UP`, `SPDS_SCAN_RATE_MOBILE`, `SPDS_SCAN_RATE_PC`, `SPDS_DEL`, `SPDS_CREATE_DATETIME`) "
				+ "VALUES (?,?,?,?,?,?,?,?,?,'0', now());";
		Db.update(insertSql, statis_date, shopId, item_id, dsr, manual_rate, review_cnt, review_cnt_up, scan_rate_mobile, scan_rate_pc);
	}

}
